/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.sss.tools;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;

/**
 * The main class responsible for database operations.
 * @author Przemyslaw Wojtysiak
 */
public class SssHelper {
    Session session = null;
    /**
     * SssHelper class constructor.
     */
    public SssHelper() {
        this.session = HibernateUtil.getSessionFactory().openSession();
    }
    
    /**
     * The method returns a list ProductShop for any given store.
     * @param idshop
     * @return list of ProductShop
     */
    public List getProductShopByIdShop(int idshop){
        List<ProductShop> productShopList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select productShop from ProductShop as productShop, Product as product "
                    + "where productShop.product = product.idproduct and productShop.shop="+idshop+" and product.actual = 1");
            productShopList = (List<ProductShop>) q.list();
        } catch (Exception e) {
            
        }
        
        return productShopList;
    }
    
    /**
     * The method returns a list ProductShop for the shop and for a given product name. 
     * In the case of idShop = -1 returns a list of ProductShop for all stores.
     * @param nameProduct
     * @param idShop
     * @return list of ProductShop
     */
    public List getProductShopByNameProduct(String nameProduct, int idShop){
        List<ProductShop> productShopList = null;
        try {
            
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = null;
            if (idShop == -1) {
                q = session.createQuery("select productShop from ProductShop as productShop, Product as product order by product.idproduct "
                    + "desc where productShop.product = product.idproduct and product.name ='"+nameProduct+"'");
            }
            else {
                q = session.createQuery("select productShop from ProductShop as productShop, Product as product, Shop as shop order by product.idproduct "
                    + "desc where productShop.product = product.idproduct and product.name ='"+nameProduct+"' and "+
                        "shop.idshop="+idShop+" and shop.idshop=productShop.shop");
            }
            productShopList = (List<ProductShop>) q.list();
        } catch (Exception e) {
            
        }
        
        return productShopList;
    }
    
    /**
     * The method returns a list containing all the stores in the database.
     * @return list of Shop
     */
    public List getShops() {
        List<Shop> shopList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery ("from Shop");
            shopList = (List<Shop>) q.list();
            session.close();
        } catch (Exception e) {
        }
        return shopList;
    }
    
    /**
     * The method returns a list containing all ProductShop in the database.
     * @return
     */
    public List getProductShop(){
        List<ProductShop> productShopList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from ProductShop");
            productShopList = (List<ProductShop>) q.list();
        } catch (Exception e) {
            
        }
        
        return productShopList;
    }
    
    /**
     * The method returns a list containing the given number of ProductShop in the database.
     * @param numberOfProductShops
     * @return list of ProductShop
     */
    public List getRightAmountProductShop(int numberOfProductShops){
        List<ProductShop> productShopList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("select productShop from ProductShop as productShop, Product as product order by product.idproduct "
                    + "desc where productShop.product = product.idproduct and product.actual = 1").setFirstResult(0).setMaxResults(numberOfProductShops);
            productShopList = (List<ProductShop>) q.list();
        } catch (Exception e) {
            
        }
        
        return productShopList;
    }
    
    /**
     * The method returns a list containing all Product in the database.
     * @return list of Product
     */
    public List getProduct(){
        List<Product> productList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Product");
            productList = (List<Product>) q.list();
            session.close();
        } catch (Exception e) {
            
        }
        
        return productList;
    }
    
    /**
     * The method returns a list containing all ProductUser in the database.
     * @return list of ProductUser
     */
    public List getProductUser(){
        List<ProductUser> productUserList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from ProductUser");
            productUserList = (List<ProductUser>) q.list();
            session.close();
        } catch (Exception e) {
            
        }
        return productUserList;
    }
    
    
    /**
     * The method returns a list containing all User in the database.
     * @return list of User
     */
    public List getUser(){
        List<User> userList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from User");
            userList = (List<User>) q.list();
            session.close();
        } catch (Exception e) {
            
        }
        return userList;
    }
    
    /**
     * Method returns UserPermision for a given permission.
     * @param permission
     * @return UserPermision
     */
    public UserPermision findUserPermission(String permission) {
        UserPermision userPermission = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from UserPermision as userPermision where userPermision.name='"+permission+"'");
            userPermission = (UserPermision) q.uniqueResult();
        } catch (Exception e) {
            
        }
        return userPermission;
    }
    
    /**
     * Method adds a new product
     * @param fb_uid
     * @param product
     * @param shop
     * @return
     * 0 - existed in database
     * 1 - added corectly
     */
    public int addNewProduct(long fb_uid, Product product, Shop shop) {
        org.hibernate.Transaction tx = session.beginTransaction();
        Query q = session.createQuery("from User as user where user.fbUid="+fb_uid);
        User user = (User) q.uniqueResult();
        Date date = new Date();
        SimpleDateFormat dateformat = new SimpleDateFormat("dd-MM-yyyy");
        StringBuilder dateNow = new StringBuilder( dateformat.format(date));

        String dateString = dateNow.toString();
        product.setData(dateString);
        product.setPlus(0);
        product.setActual(true);
        System.out.println(shop.getIdshop().toString());
        Query q2 = session.createQuery("select productShop from Product as product, "+
                "ProductShop as productShop where product.name='"+product.getName()+
                "' and " + "product.price="+product.getPrice() + " and " +
                "product.brand='"+product.getBrand() + "' and "+
                "product.idproduct=productShop.product and " + 
                "productShop.shop="+shop.getIdshop() + " and " +
                "product.actual = 1");
        
        if (!q2.list().isEmpty())
        {
            session.close();
            return 0;
        }
        
        ProductShop productShop = new ProductShop(shop, product);
        ProductUser productUser = new ProductUser(user, product);
        session.save(product);
        session.save(productShop);
        session.save(productUser);
        
        tx.commit();
        session.close();
        return 1;
    }
    
    /**
     * This method adds a new user
     * @param fb_uid
     * @param permission
     * @return
     */
    public User addNewUser(long fb_uid, String permission)
    {
        User user = new User();
        user.setFbUid(fb_uid);
        user.setUserPermision(findUserPermission(permission));
        session.save(user);
        return user;
    }

    /**
     * The method returns the names of all the shops in the city.
     * @param name of city
     * @return list of String
     */
    public List getShopName(String name) {
        List<String> shopNameList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery ("select distinct shop.name from Shop as shop where shop.city='"+name+"'");
            shopNameList = (List<String>) q.list();
            session.close();
        } catch (Exception e) {
        }
        return shopNameList;
    }

    /**
     * The method returns a list of Shop store for a given name.
     * @param name
     * @return list of Shop
     */
    public List getShopByName(String name) {
        List<Shop> shopByNameList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery ("from Shop as shop where shop.name='"+name+"'");
            shopByNameList = (List<Shop>) q.list();
            session.close();
        } catch (Exception e) {
        }
        return shopByNameList;
    }
    
    /**
     * The method returns list of Shop of all the shops in the city.
     * @param name
     * @return list of Shop
     */
    public List getShopByCity(String name) {
        List<Shop> shopByNameList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery ("from Shop as shop where shop.city='"+name+"'");
            shopByNameList = (List<Shop>) q.list();
        } catch (Exception e) {
        }
        return shopByNameList;
    }

    /**
     * This method adds a new store. / / Method for the Administrator.
     * @param shop
     */
    public void addNewShop(Shop shop) {
        org.hibernate.Transaction tx = session.beginTransaction();
        session.save(shop);
        tx.commit();
        session.close();
    }

    /**
     * The method of editing the existing product. // For future functionality.
     * @param idProduct
     * @param newProduct
     */
    public void editProduct(int idProduct, Product newProduct) {
        Product foundProduct = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Product as product where product.idproduct='"+idProduct+"'");
            foundProduct = (Product) q.uniqueResult();
            
            foundProduct.setName(newProduct.getName());
            foundProduct.setBrand(newProduct.getBrand());
            foundProduct.setData(newProduct.getData());
            foundProduct.setHowPacket(newProduct.getHowPacket());
            foundProduct.setInfo(newProduct.getInfo());
            foundProduct.setPrice(newProduct.getPrice());
            foundProduct.setActual(newProduct.isActual());
            session.update(foundProduct);
            tx.commit();
            session.close();
            
        } catch (Exception e) {
            
        }
    }

    
    /**
     * The method of verifying the user's permissions (now only for normal user and new user). 
     * For future  for admin, owner shop and blocked user.
     * If the user has logged on for the first time the application 
     * is automatically added to the database with the status of "normal user"
     * @param fb_uid
     * @return
     * 0 - blocked user
     * 1 - normal user
     * 2 - admin
     * 3 - owner shop
     * 4 - new user (normal user)
     */
    public int checkUser(long fb_uid) {
        org.hibernate.Transaction tx = session.beginTransaction();
        Query q = session.createQuery("from User as user where user.fbUid="+fb_uid);
        User user = new User();
        if (!q.list().isEmpty())
        {
            user = (User) q.uniqueResult();
            Query q2 = session.createQuery("select warningUser.value from WarningUser as warningUser where warningUser.idUser="+user.getIduser());
            if (!q2.list().isEmpty())
            {
                int numberOfWarning = Integer.parseInt(q2.uniqueResult().toString());
                if (numberOfWarning == 3) {
                    session.close();
                    return 0;
                }
                else
                {
                    session.close();
                    return user.getUserPermision().getIduserPermision();
                }
            }
            else
            {
                session.close();
                return user.getUserPermision().getIduserPermision();
            }
        }
        else
        {
            user = addNewUser(fb_uid, "normal user");
        }
        tx.commit();
        session.close();
        return 4;
    }


    /**
     * The method responsible for adding / subtracting points for the product.
     * @param idProduct
     * @param fb_uid
     * @param value
     * @return
     * 0 - product was plused before
     * 1 - plus product correctly
     * 2 - some error - if product didn't exist in database
     */
    public int plusProduct(int idProduct, long fb_uid, int value) {
        org.hibernate.Transaction tx = session.beginTransaction();
        Query q = session.createQuery("from User as user where user.fbUid="+fb_uid);
        User user = (User) q.uniqueResult();
        int idUser = user.getIduser();
        
        Query q2 = session.createQuery("from ProductPlus as productPlus where productPlus.idProduct="+idProduct+" and "+
                "productPlus.idUser="+idUser);
        if (!q2.list().isEmpty()) {
            session.close();
            return 0;
        }
        ProductPlus productPlus = new ProductPlus(idProduct, idUser);
        session.save(productPlus);
        Query q3 = session.createQuery("from Product as product where product.idproduct="+idProduct);
        if (q3.list().isEmpty()) {
            session.close();
            return 2;
        }
        Product product = (Product) q3.uniqueResult();
        product.setPlus(product.getPlus() + value);
        session.update(product);
        tx.commit();
        session.close();
        return 1;
    }
    
    /**
     * The method returns a list of Product of the given name of the product.
     * @param name
     * @return list of Product
     */
    public List getProductByName(String name) {
        List<Product> productList = null;
        try {
            org.hibernate.Transaction tx = session.beginTransaction();
            Query q = session.createQuery("from Product as product where product.name='"+name+"'");
            
            productList = (List<Product>) q.list();
        } catch (Exception e) {
            
        }
        
        return productList;
    }
    
    /**
     * The method for starting the main algorithm to search for stores with products with an optimal price.
     * @param x
     * @param y
     * @param products
     * @param city
     * @param country
     * @param transport
     * @param time
     * @return list of ProductShop
     */
    public List searchProduct(Double x, Double y, List<String> products, String city, String country, int transport, int time) {
        List<ProductShop> productsShops = new ArrayList<ProductShop>();
        org.hibernate.Transaction tx = session.beginTransaction();
        
        for (String name : products) {
            Query q = session.createQuery("select productShop from Product as product, "+
                "ProductShop as productShop, Shop as shop where product.name='"+name +
                "' and " +
                "productShop.product=product.idproduct and " + 
                "productShop.shop=shop.idshop and product.actual = 1 and " + 
                    "shop.city='"+city+"'" + " and shop.country='"+country+"'");
            if (!q.list().isEmpty()) {
                List<ProductShop> ps = (List<ProductShop>)q.list();
                for (ProductShop productShop : ps) {
                    productsShops.add(productShop);
                }
            }
        }
        
        if (productsShops.isEmpty()) {
            Shop shop = new Shop("none", "none", 0, "none", "none", 0.0, 0.0);
            shop.setIdshop(1);
            Product product = new Product("none",0.0,"none","none","none","none",0,false);
            product.setIdproduct(0);
            ProductShop ps3 = new ProductShop(shop, product);
            ps3.setIdproductShop(0);
            productsShops.add(ps3);
            return productsShops;
        }
        
        List<ProductShop> productsShop = null;
        if (!productsShops.isEmpty()) {
            Double timeInMinutes = Double.parseDouble(Integer.toString(time * 60));
            SearchProductAlgorithm searchProductAlgorithm = new SearchProductAlgorithm(transport, timeInMinutes);
            searchProductAlgorithm.init(productsShops, x, y);
            productsShop = searchProductAlgorithm.getShops();
        }
        
        if (productsShop.isEmpty())
        {
            Shop shop = new Shop("none", "none", 0, "none", "none", 0.0, 0.0);
            shop.setIdshop(0);
            Product product = new Product("none",0.0,"none","none","none","none",0,false);
            product.setIdproduct(1);
            ProductShop ps3 = new ProductShop(shop, product);
            ps3.setIdproductShop(0);
            productsShop.add(ps3);
        }
        else {
            for (int i = 0; i < productsShop.size(); i++) {
                productsShop.get(i).setIdproductShop(i+1);
            }
        }
        return productsShop;
    }

    /**
     * The method returns a list of Shop for shops located within 200m from the given coordinates.
     * @param x
     * @param y
     * @param city
     * @return
     */
    public List<Shop> getShopByCoordinates(Double x, Double y, String city) {
        List<Shop> shops = getShopByCity(city);
        List<Shop> shops2 = new ArrayList<Shop>();
        for (int i = 0; i < shops.size(); i++) {
            if (countDistance(x, y, shops.get(i).getX(), shops.get(i).getY()) <= 200.0) {
                shops2.add(shops.get(i));
            }
        }
        return shops2;
    }
    
    /**
     * Auxiliary method to calculate the distance in meters between two points.
     * @param x1
     * @param y1
     * @param x2
     * @param y2
     * @return distance in meters
     */
    private double countDistance(double x1, double y1, double x2, double y2) {
        double theta = y1 - y2;
        double dist = Math.sin(deg2rad(x1)) * Math.sin(deg2rad(x2)) + Math.cos(deg2rad(x1)) * Math.cos(deg2rad(x2)) * Math.cos(deg2rad(theta));
        dist = Math.acos(dist);
        dist = rad2deg(dist);
        dist = dist * 60 * 1.1515;
        dist = dist * 1.609344;
        return dist * 1000;
    }
    
    /**
     * Auxiliary method to calculate the distance
     * @param deg
     * @return 
     */
    private double deg2rad(double deg) {
        return (deg * Math.PI / 180.0);
    }

    /**
     * Auxiliary method to calculate the distance
     * @param rad
     * @return 
     */
    private double rad2deg(double rad) {
        return (rad * 180.0 / Math.PI);
    }

    /**
     * The method returns a list of the Product for any given store.
     * @param shop
     * @return list of Product
     */
    public List getProductsByShop(Shop shop) {
        List<Product> productList = null;
        org.hibernate.Transaction tx = session.beginTransaction();
        Query q2 = session.createQuery("select product from Product as product, "+
                "ProductShop as productShop where "+
                "product.idproduct=productShop.product and " + 
                "productShop.shop="+shop.getIdshop() + " and " +
                "product.actual = 1");
        
        productList = (List<Product>)q2.list();
        session.close();
        return productList;
    }
    
    
    /**
     * The method returns a list of product names for a given user.
     * @param fb_uid
     * @return list of String
     */
    public List checkSavedShoppingList(long fb_uid) {
        List<String> result = null;
        String oneLineResult;
        org.hibernate.Transaction tx = session.beginTransaction();
        Query q1 = session.createQuery("select saved.value from ShoppingList as saved, User as user "+
                "where user.fbUid="+fb_uid+" and user.iduser=saved.idUser");
        if (q1.list().isEmpty()) {
            result = new ArrayList<String>();
            result.add("none");
            return result;
        }
        if (((String) q1.list().get(0)).equals("")) {
            result = new ArrayList<String>();
            result.add("none");
            return result;
        }
        oneLineResult = (String) q1.list().get(0);
        String[] tmp = oneLineResult.split(";");
        result = new ArrayList<String>();
        result.addAll(Arrays.asList(tmp));
        return result;
    }

    /**
     * Method of Writing a new shopping list for that user.
     * @param fb_uid
     * @param shoppingList
     * @return
     */
    public int saveShoppingList(long fb_uid, String shoppingList) {
        org.hibernate.Transaction tx = session.beginTransaction();
        
        Query q = session.createQuery("from User as user where user.fbUid="+fb_uid);
        User user = (User) q.uniqueResult();
        int idUser = user.getIduser();
        
        Query q1 = (Query) session.createQuery("select saved from ShoppingList as saved, User as user "+
                "where user.iduser="+idUser+" and user.iduser=saved.idUser");
        if (q1.list().isEmpty()) {
            ShoppingList saved = new ShoppingList(idUser,shoppingList);
            session.save(saved);
            tx.commit();
            session.close();
            return 1;
        }
        ShoppingList saved = (ShoppingList) q1.uniqueResult();
        saved.setValue(shoppingList);
        session.update(saved);
        tx.commit();
        session.close();
        return 1;
    }
}
